﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using QuanLyBanVeMayBay.Resources;

namespace QuanLyBanVeMayBay
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }

        private void Form2_FormClosed(object sender, FormClosedEventArgs e)
        {
            Application.Exit();
        }

        private void tab_tuyen_bay_Click(object sender, EventArgs e)
        {
            tb_quanlysanbay_masanbay.Clear();
            tb_quanlysanbay_tensanbay.Clear();
            tb_quanlysanbay_diachi.Clear();
            // load tab quan ly san bay
            xulydulieu xuly = new xulydulieu();
            string sql = "select * from SANBAY";
            dataGridView_sanbay.DataSource = xuly.taobang(sql);
            dataGridView_sanbay.Columns[0].HeaderText = "Mã Sân Bay";
            dataGridView_sanbay.Columns[1].HeaderText = "Tên Sân Bay";
            dataGridView_sanbay.Columns[2].HeaderText = "Địa Chỉ";
            tb_quanlysanbay_masanbay.Clear();
            tb_quanlysanbay_tensanbay.Clear();
            tb_quanlysanbay_diachi.Clear();

        }

        // thêm sân bay --------------------------------------------------------
        private void bt_quanlysanbay_them_Click(object sender, EventArgs e)
        {
            if (tb_quanlysanbay_masanbay.Text == "" || tb_quanlysanbay_tensanbay.Text == "" || tb_quanlysanbay_diachi.Text == "")
            {
                MessageBox.Show("Chưa điền đủ thông tin", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            else
            {
                try
                {
                    string sql = "insert into SANBAY values('" + tb_quanlysanbay_masanbay.Text + "',N'" 
                        + tb_quanlysanbay_tensanbay.Text + "',N'" + tb_quanlysanbay_diachi.Text + "')";

                    xulydulieu xuly = new xulydulieu();
                    xuly.nonquery(sql);
                    // cập nhật lại datagridview-------------------------------------
                    string sqll = "select * from SANBAY";
                    dataGridView_sanbay.DataSource = xuly.taobang(sqll);
                    tb_quanlysanbay_masanbay.Clear();
                    tb_quanlysanbay_tensanbay.Clear();
                    tb_quanlysanbay_diachi.Clear();
                }
                catch (System.Exception ex)
                {
                    MessageBox.Show("Đã tồn tại mã sân bay này !", "Lỗi Xử Lý");
                }
               
            }
         
        }

        //lấy giá trị trong bảng sân bay đưa lên các textbox ----------------------------------
        private void dataGridView_sanbay_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                int dong;
                dong = e.RowIndex;
                tb_quanlysanbay_masanbay.Text = dataGridView_sanbay.Rows[dong].Cells[0].Value.ToString();
                tb_quanlysanbay_tensanbay.Text = dataGridView_sanbay.Rows[dong].Cells[1].Value.ToString();
                tb_quanlysanbay_diachi.Text = dataGridView_sanbay.Rows[dong].Cells[2].Value.ToString();
            }
            catch (System.Exception ex)
            {
            	
            }


        }

        //sửa thông tin sân --------------------------------------------------------------------
        private void bt_quanlysanbay_sua_Click(object sender, EventArgs e)
        {
            try
            {
                string sql = "update SANBAY set TenSanBay = N'" + tb_quanlysanbay_tensanbay.Text+ "', DiaChi = N'"
                    + tb_quanlysanbay_diachi.Text+ "' Where MaSanBay ='" + tb_quanlysanbay_masanbay.Text + "'";

                xulydulieu xuly = new xulydulieu();
                xuly.nonquery(sql);
                //cập nhật lại datadridview-----------------------------------------------------
                string sqll = "select * from SANBAY";
                dataGridView_sanbay.DataSource = xuly.taobang(sqll);
                tb_quanlysanbay_masanbay.Clear();
                tb_quanlysanbay_tensanbay.Clear();
                tb_quanlysanbay_diachi.Clear();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("Thông tin lỗi:\n" + ex.Message, "Lỗi Xử Lý");
            }
   
        }

        //xóa sân bay -----------------------------------------------------------------------------
        int curen_row = 0;
        private void bt_quanlysanbay_xoa_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Khi xóa sân bay có thể sẽ xóa luôn chuyến bay liên quan", "thông báo", MessageBoxButtons.OKCancel) == DialogResult.OK)
            {
                try
                {
                    xulydulieu xuly = new xulydulieu();
                    int temp = dataGridView_quanlychuyenbay.Rows.Count;

                    string sql = "delete from CT_CHUYENBAY where MaSanBayTrungGian = '" + tb_quanlysanbay_masanbay.Text + "'";
                    xuly.nonquery(sql);

                    for (int i = 0; i < temp; i++)
                       if (dataGridView_quanlychuyenbay.Rows[i].Cells[1].Value.ToString().Substring(0, 3) == tb_quanlysanbay_masanbay.Text.Substring(0,3) ||
                            dataGridView_quanlychuyenbay.Rows[i].Cells[1].Value.ToString().Substring(4, 3) == tb_quanlysanbay_masanbay.Text.Substring(0,3))
                        {
                            curen_row = i;
                           if( MessageBox.Show("Phát hiện xóa chuyến bay liên quan mang mã " +
                               dataGridView_quanlychuyenbay.Rows[i].Cells[1].Value.ToString(), "Cảnh báo",MessageBoxButtons.OKCancel) == DialogResult.OK)
                            bt_quanlychuyenbay_xoa.PerformClick();
                           else
                               break;
                        }
                    string sql1 = "delete from TUYENBAY where MaSanBayDi ='" + tb_quanlysanbay_masanbay.Text + "' or MaSanBayDen ='" +
                    tb_quanlysanbay_masanbay.Text + "'";
                    xuly.nonquery(sql1);

                    string sql2 = "delete from SANBAY where MaSanBay ='" + tb_quanlysanbay_masanbay.Text + "'";
                    xuly.nonquery(sql2);

                    //cập nhật lại datadridview-----------------------------------------------------
                    string sqll = "select * from SANBAY";
                    dataGridView_sanbay.DataSource = xuly.taobang(sqll);
                    tb_quanlysanbay_masanbay.Clear();
                    tb_quanlysanbay_tensanbay.Clear();
                    tb_quanlysanbay_diachi.Clear();
                }
                catch (System.Exception ex)
                {
                   // MessageBox.Show(ex.Message);
                }
            }
            else
                return;
            
        }

        private void superTabControlPanel10_Click(object sender, EventArgs e)
        {

        }

        //đổi mật khẩu admin-------------------------------------------------------------
        private void bt_doimatkhau_ok_Click(object sender, EventArgs e)
        {
            if (tb_doimatkhau_mkcu.Text == "" || tb_doimatkhau_mkmoi.Text == "" || tb_doimatkhau_llmkmoi.Text == "")
            {
                MessageBox.Show("Chưa điền hết thông tin", "Lỗi !", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            else
            {
                xulydulieu conn = new xulydulieu();
                DataTable taikhoan = new DataTable();
                string sql = "SELECT * From TAIKHOAN WHERE PassWord ='" + tb_doimatkhau_mkcu.Text+"'";
                taikhoan = conn.taobang(sql);
                if (taikhoan.Rows.Count != 1)
                {
                    MessageBox.Show("Mật khẩu cũ không đúng, vui lòng kiểm tra lại", "Lỗi");
                }
                else
                {
                    if (tb_doimatkhau_mkmoi.Text != tb_doimatkhau_llmkmoi.Text)
                    {
                        MessageBox.Show("Mật khẩu mới không trùng nhau", "Lỗi");
                    }
                    else
                    {
                        string sqll = "update TAIKHOAN set PassWord = N'" + tb_doimatkhau_mkmoi.Text + "' where UserName = 'admin'";
                        conn.nonquery(sqll);
                        MessageBox.Show("Đã đổi mật khẩu !");
                        tb_doimatkhau_mkcu.Clear();
                        tb_doimatkhau_mkmoi.Clear();
                        tb_doimatkhau_llmkmoi.Clear();
                    }
                }
            }
        }

        private void Form2_Load(object sender, EventArgs e)
        {
            xulydulieu xuly = new xulydulieu();

            //load tab quan ly tuyen bay
            string sql1 = "select * from TUYENBAY";
            dataGridView_tuyenbay.DataSource = xuly.taobang(sql1);
            dataGridView_tuyenbay.Columns[0].HeaderText = "Mã Tuyến Bay";
            dataGridView_tuyenbay.Columns[1].HeaderText = "Mã Sân Bay Đi";
            dataGridView_tuyenbay.Columns[2].HeaderText = "Mã Sân Bay Đến";

            // load tab nhan lich chuyen bay
            string laysanbay = "select TenSanBay from SANBAY";
            cb_nhanlichchuyenbay_sanbaydi.DataSource = xuly.taobang(laysanbay);
            cb_nhanlichchuyenbay_sanbaydi.DisplayMember = "TenSanBay";
            cb_nhanlichchuyenbay_sanbayden.DataSource = xuly.taobang(laysanbay);
            cb_nhanlichchuyenbay_sanbayden.DisplayMember = "TenSanBay";

            //tạo số sân trung gian theo quy định ----------------------------
            string sql = "select SoSanBayTrungGianToiDa from THAMSO";
            dataGridView_sanbaytrunggian.Rows.Add((int) xuly.taobang(sql).Rows[0][0] - 1);

            //danh sách chuyến bay
            string sql2 = "select RTRIM(MaChuyenBay) as MaChuyenBay, MaTuyenBay,NgayGioKhoiHanh,ThoiLuong,GiaSan from CHUYENBAY";
            dataGridView_quanlychuyenbay.DataSource = xuly.taobang(sql2);
            dataGridView_quanlychuyenbay.Columns[0].HeaderText = "Mã chuyến bay";
            dataGridView_quanlychuyenbay.Columns[1].HeaderText = "Mã tuyến bay";
            dataGridView_quanlychuyenbay.Columns[2].HeaderText = "Ngày giờ khởi hành";
            dataGridView_quanlychuyenbay.Columns[3].HeaderText = "Thời lượng";
            dataGridView_quanlychuyenbay.Columns[4].HeaderText = "Giá sàn";
            //load user
            string sql3 = "select UserName from TAIKHOAN";
            dataGridView_taikhoan.DataSource = xuly.taobang(sql3);
            dataGridView_taikhoan.Columns[0].HeaderText = "Tên Tài Khoản";
        }

        //cập nhật mã tuyến bay khi chọn sân bay -------------------------------------------
        private void cb_tuyenbay_sanbaydi_SelectedIndexChanged(object sender, EventArgs e)
        {
            xulydulieu xuly = new xulydulieu();
            string sql = "select RTRIM(MaSanBay) as MaSanBay from SANBAY where TenSanBay = N'" + cb_tuyenbay_sanbaydi.Text + "'";
            tb_tuyenbay_masbdi.Text = xuly.taobang(sql).Rows[0][0].ToString();
            tb_tuyenbay_matuyenbay.Text = tb_tuyenbay_masbdi.Text + "-" + tb_tuyenbay_masbden.Text;
        }

        private void cb_tuyenbay_sanbayden_SelectedIndexChanged(object sender, EventArgs e)
        {
            xulydulieu xuly = new xulydulieu();
            string sql = "select RTRIM(MaSanBay) as MaSanBay from SANBAY where TenSanBay = N'" + cb_tuyenbay_sanbayden.Text + "'";
            tb_tuyenbay_masbden.Text = xuly.taobang(sql).Rows[0][0].ToString();
            tb_tuyenbay_matuyenbay.Text = tb_tuyenbay_masbdi.Text + "-" + tb_tuyenbay_masbden.Text;
        }

        // sau khi chuyển tab, các tên sân bay và mã sân bay đc load lên--------------------------------------------------
        private void tab_quan_ly_tuyen_bay_Click(object sender, EventArgs e)
        {
            xulydulieu xuly = new xulydulieu();
            string laysanbay = "select TenSanBay from SANBAY";
            cb_tuyenbay_sanbaydi.DataSource = xuly.taobang(laysanbay);
            cb_tuyenbay_sanbaydi.DisplayMember = "TenSanBay";
            cb_tuyenbay_sanbayden.DataSource = xuly.taobang(laysanbay);
            cb_tuyenbay_sanbayden.DisplayMember = "TenSanBay";

            string sql1 = "select RTRIM(MaSanBay) as MaSanBay from SANBAY where TenSanBay = N'" + cb_tuyenbay_sanbaydi.Text + "'";
            string sql2 = "select RTRIM(MaSanBay) as MaSanBay from SANBAY where TenSanBay = N'" + cb_tuyenbay_sanbayden.Text + "'";
            tb_tuyenbay_masbdi.Text = xuly.taobang(sql1).Rows[0][0].ToString();
            tb_tuyenbay_masbden.Text = xuly.taobang(sql2).Rows[0][0].ToString();
            tb_tuyenbay_matuyenbay.Text = tb_tuyenbay_masbdi.Text + "-" + tb_tuyenbay_masbden.Text;

            //load tab quan ly tuyen bay
            string sql3 = "select * from TUYENBAY";
            dataGridView_tuyenbay.DataSource = xuly.taobang(sql3);
            dataGridView_tuyenbay.Columns[0].HeaderText = "Mã Tuyến Bay";
            dataGridView_tuyenbay.Columns[1].HeaderText = "Mã Sân Bay Đi";
            dataGridView_tuyenbay.Columns[2].HeaderText = "Mã Sân Bay Đến";
        }

        private void tab_quan_ly_san_bay_Click(object sender, EventArgs e)
        {
          
        }

        //thêm tuyến bay ----------------------------------------------------------------------------------------
        private void bt_chuyenbay_them_Click(object sender, EventArgs e)
        {
            if (cb_tuyenbay_sanbaydi.Text == cb_tuyenbay_sanbayden.Text)
            {
                MessageBox.Show("Sân bay đi và sân bay đến không được trùng nhau", "Lỗi!");
            }
            else
            {
                try
                {
                    string sql = "insert into TUYENBAY values('" + tb_tuyenbay_matuyenbay.Text + "',N'" 
                        + tb_tuyenbay_masbdi.Text + "',N'" + tb_tuyenbay_masbden.Text + "')";

                    xulydulieu xuly = new xulydulieu();
                    xuly.nonquery(sql);
                    string sqll = "select * from TUYENBAY";
                    dataGridView_tuyenbay.DataSource = xuly.taobang(sqll);      
                }
                catch (System.Exception ex)
                {
                    MessageBox.Show("Đã tồn tại mã tuyến bay này !", "Lỗi Xử Lý");
                }
               
            }
        }

        private void dataGridView_tuyenbay_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                int dong;
                dong = e.RowIndex;
                tb_tuyenbay_matuyenbay.Text = dataGridView_tuyenbay.Rows[dong].Cells[0].Value.ToString();
                tb_tuyenbay_masbdi.Text = dataGridView_tuyenbay.Rows[dong].Cells[1].Value.ToString();
                tb_tuyenbay_masbden.Text = dataGridView_tuyenbay.Rows[dong].Cells[2].Value.ToString();

                xulydulieu xuly = new xulydulieu();
                string sql = "select TenSanBay from SANBAY where MaSanBay='" + tb_tuyenbay_masbdi.Text+"'";
                cb_tuyenbay_sanbaydi.Text = xuly.taobang(sql).Rows[0][0].ToString();
                string sql1 = "select TenSanBay from SANBAY where MaSanBay='" + tb_tuyenbay_masbden.Text + "'";
                cb_tuyenbay_sanbayden.Text = xuly.taobang(sql1).Rows[0][0].ToString();
            }
            catch (System.Exception ex)
            {

            }
        }


        //xóa tuyến bay ----------------------------------------------------------------------------------
        private void bt_chuyenbay_xoa_Click(object sender, EventArgs e)
        {
             if (MessageBox.Show("Khi xóa tuyến bay có thể sẽ xóa luôn chuyến bay liên quan", "thông báo", MessageBoxButtons.OKCancel) == DialogResult.OK)
            {
                 try
                 {
                     xulydulieu xuly = new xulydulieu();
                     int temp = dataGridView_quanlychuyenbay.Rows.Count;

                     for (int i = 0; i < temp; i++)
                         if (dataGridView_quanlychuyenbay.Rows[i].Cells[1].Value.ToString() == tb_tuyenbay_matuyenbay.Text ||
                              dataGridView_quanlychuyenbay.Rows[i].Cells[1].Value.ToString() == tb_tuyenbay_matuyenbay.Text)
                         {
                             curen_row = i;
                             if (MessageBox.Show("Phát hiện xóa chuyến bay liên quan mang mã " +
                                 dataGridView_quanlychuyenbay.Rows[i].Cells[1].Value.ToString(), "Cảnh báo", MessageBoxButtons.OKCancel) == DialogResult.OK)
                                 bt_quanlychuyenbay_xoa.PerformClick();
                             else
                                 break;
                         }
                     string sql1 = "delete from TUYENBAY where MaTuyenBay ='" + tb_tuyenbay_matuyenbay.Text + "'";
                     xuly.nonquery(sql1);

                     string sql2 = "select * from TUYENBAY";
                     dataGridView_tuyenbay.DataSource = xuly.taobang(sql2);
                 }
                 catch (System.Exception ex)
                 {
                 	
                 }

            }
       
        }

        //cập nhật lại danh sách sân bay ------------------------------------------------
        private void tab_chuyen_bay_Click(object sender, EventArgs e)
        {
            xulydulieu xuly = new xulydulieu();
            string laysanbay = "select TenSanBay from SANBAY";
            cb_nhanlichchuyenbay_sanbaydi.DataSource = xuly.taobang(laysanbay);
            cb_nhanlichchuyenbay_sanbaydi.DisplayMember = "TenSanBay";
            cb_nhanlichchuyenbay_sanbayden.DataSource = xuly.taobang(laysanbay);
            cb_nhanlichchuyenbay_sanbayden.DisplayMember = "TenSanBay";

            dataGridView_sanbaytrunggian.Rows.Clear();
            a = 0;
            string sql = "select SoSanBayTrungGianToiDa from THAMSO";
            dataGridView_sanbaytrunggian.Rows.Add((int)xuly.taobang(sql).Rows[0][0] - 1);

        }

        //mở bảng thêm sân bay---------------------------------------
        public void GetFirstValue(string value)
        {

            cb_nhanlichchuyenbay_sanbaydi.Text = value;

        } 
        private void bt_themsanbaydi_Click(object sender, EventArgs e)
        {
            ThemSanBay san = new ThemSanBay();
            san.tensan = new ThemSanBay.tensanbay(GetFirstValue);
            san.ShowDialog();
        }
        //thêm sân bay đến --- hiện ra bảng thêm tuyến bay-----------------------------------------------
        public void GetsecondValue(string value)
        {

            cb_nhanlichchuyenbay_sanbayden.Text = value;

        }
        private void bt_themsanbayden_Click(object sender, EventArgs e)
        {
            ThemSanBay san = new ThemSanBay();
            san.tensan = new ThemSanBay.tensanbay(GetsecondValue);
            san.ShowDialog();
        }

        //hiển thị các quy định ---------------------------------------
        private void tab_quy_dinh_Click(object sender, EventArgs e)
        {
            string sql = "select ThoiGianBayToiThieu,SoSanBayTrungGianToiDa,"
            + "ThoiGianDungToiThieu,ThoiGianDungToiDa,ThoiGianDatVeChamNhat,ThoiGianHuyVe from THAMSO";
            xulydulieu xuly = new xulydulieu();
            dataGridView_quydinh.DataSource = xuly.taobang(sql);
            dataGridView_quydinh.Columns[0].HeaderText = "Thời gian bay tối thiểu";
            dataGridView_quydinh.Columns[1].HeaderText = "Sân bay trung gian tối đa";
            dataGridView_quydinh.Columns[2].HeaderText = "Thời gian dừng tối thiểu";
            dataGridView_quydinh.Columns[3].HeaderText = "Thời gian dừng tối đa";
            dataGridView_quydinh.Columns[4].HeaderText = "Thời gian đặt vé chậm nhất";
            dataGridView_quydinh.Columns[5].HeaderText = "Thời gian hủy vé chậm nhất";
            tg_baytoithieu.Text = xuly.taobang(sql).Rows[0][0].ToString();
            sosan_trunggiantoida.Text = xuly.taobang(sql).Rows[0][1].ToString();
            tg_dungtoithieu.Text = xuly.taobang(sql).Rows[0][2].ToString();
            tg_dungtoida.Text = xuly.taobang(sql).Rows[0][3].ToString();
            tg_datvechamnhat.Text = xuly.taobang(sql).Rows[0][4].ToString();
            tg_huydatve.Text = xuly.taobang(sql).Rows[0][5].ToString();
        }

        //thay đổi quy định --------------------------------------------------------------------
        private void bt_thay_doi_Click(object sender, EventArgs e)
        {
            try
            {
                string sql = "update THAMSO set ThoiGianBayToiThieu = '" + tg_baytoithieu.Text
                        + "', SoSanBayTrungGianToiDa ='" + sosan_trunggiantoida.Text
                     + "', ThoiGianDungToiThieu ='" + tg_dungtoithieu.Text
                     + "', ThoiGianDungToiDa = '" + tg_dungtoida.Text
                      + "', ThoiGianDatVeChamNhat = '" + tg_datvechamnhat.Text
                      + "', ThoiGianHuyVe ='" + tg_huydatve.Text + "' where STT = 'True'";
                xulydulieu xuly = new xulydulieu();
                //cập nhật lại bảng quy định ------------------------------------------------
                xuly.nonquery(sql);
                string sql1 = "select ThoiGianBayToiThieu,SoSanBayTrungGianToiDa,"
                + "ThoiGianDungToiThieu,ThoiGianDungToiDa,ThoiGianDatVeChamNhat,ThoiGianHuyVe from THAMSO";
                dataGridView_quydinh.DataSource = xuly.taobang(sql1);
                dataGridView_quydinh.Columns[0].HeaderText = "Thời gian bay tối thiểu";
                dataGridView_quydinh.Columns[1].HeaderText = "Sân bay trung gian tối đa";
                dataGridView_quydinh.Columns[2].HeaderText = "Thời gian dừng tối thiểu";
                dataGridView_quydinh.Columns[3].HeaderText = "Thời gian dừng tối đa";
                dataGridView_quydinh.Columns[4].HeaderText = "Thời gian đặt vé chậm nhất";
                dataGridView_quydinh.Columns[5].HeaderText = "Thời gian hủy vé chậm nhất";
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("Lỗi");
            }

        }

        //nhận lịch chuyến bay ------------------------------------------------------------------

        private void bt_nhan_lich_chuyen_bay_Click(object sender, EventArgs e)
        {

            if (tb_nhanlichchuyenbay_machuyenbay.Text == "" || tb_nhanlichchuyenbay_giasan.Text == "" ||
                tb_nhanlichchuyenbay_gio.Text == ""|| tb_nhanlichchuyenbay_soghe1.Text == "" || 
                tb_nhanlichchuyenbay_soghe2.Text == "" || tb_nhanlichchuyenbay_thoigianbay.Text == "" 
                || nhanlichchuyenbay_ngay.Text == "")
            {
                MessageBox.Show("Chưa điền đầy đủ thông tin", "Lỗi !");
                return;
            }
            xulydulieu xuly = new xulydulieu();
            string tgbaymin= "select ThoiGianBayToiThieu from THAMSO where STT ='True'";
            if (string.Compare(tb_nhanlichchuyenbay_thoigianbay.Text, xuly.taobang(tgbaymin).Rows[0][0].ToString()) < 0)
            {
                MessageBox.Show("Thời gian bay tối thiểu phải là:\n" + xuly.taobang(tgbaymin).Rows[0][0].ToString(), "Lỗi");
                return;
            }
            DateTime date = DateTime.Parse(nhanlichchuyenbay_ngay.Text);
            string ngaydat =  date.Year.ToString()+date.Month.ToString()+date.Day.ToString();
            string hientai = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString();
            if (string.Compare(ngaydat, hientai) <= 0)
            {
                MessageBox.Show("chuyến bay phải bắt đầu sau khi đặt ít nhất 1 ngày", "Lỗi");
                return;
            }
            else
            {
                if (cb_nhanlichchuyenbay_sanbaydi.Text == cb_nhanlichchuyenbay_sanbayden.Text)
                {
                    MessageBox.Show("Sân bay đi phải khác sân bay đến", "chú ý !");
                }
                else
                {
 
                    int demsosanbaytrunggian = 0;
                    //dem so san trung gian
                    string sql = "select SoSanBayTrungGianToiDa from THAMSO";
                    int quydinhsosan = (int)xuly.taobang(sql).Rows[0][0];
                    for (int i = 0; i < quydinhsosan; i++)
                    {
                        if (dataGridView_sanbaytrunggian.Rows[i].Cells[1].Value == null)
                        {
                            demsosanbaytrunggian = i; break;
                        }
                        if ((i == quydinhsosan - 1) && (dataGridView_sanbaytrunggian.Rows[i].Cells[0].Value != null))
                        {
                            demsosanbaytrunggian = i + 1; break;
                        }

                    }
                    if (demsosanbaytrunggian > 1)
                    {
                        for (int i = 0; i < demsosanbaytrunggian - 1; i++)
                            for (int j = i + 1; j < demsosanbaytrunggian; j++)
                            {
                                if (dataGridView_sanbaytrunggian.Rows[i].Cells[0].Value.ToString() == dataGridView_sanbaytrunggian.Rows[j].Cells[0].Value.ToString())
                                {
                                    MessageBox.Show("Các sân bay trung gian phải khác nhau", "Lỗi !"); return;
                                }
                            }

                    }
                    if (demsosanbaytrunggian > 0)
                    {
                        for (int i = 0; i < demsosanbaytrunggian; i++)
                        {
                            if (dataGridView_sanbaytrunggian.Rows[i].Cells[0].Value.ToString() == cb_nhanlichchuyenbay_sanbaydi.Text
                                || dataGridView_sanbaytrunggian.Rows[i].Cells[0].Value.ToString() == cb_nhanlichchuyenbay_sanbayden.Text)
                            {
                                MessageBox.Show("Sân bay trung gian không được trùng với sân đến hoặc sân đi", "Lỗi !");
                                return;
                            }
                        }
                        // lưu chi tiết chuyến bay --------------------------------------------
                        for (int i = 0; i < demsosanbaytrunggian; i++)
                        {
                            try
                            {
                                string sql1 = "select RTRIM(MaSanBay) as MaSanBay from SANBAY where TenSanBay = N'"
                                                 + dataGridView_sanbaytrunggian.Rows[i].Cells[0].Value.ToString() + "'";
                                string sql2 = "insert into CT_CHUYENBAY values('" + tb_nhanlichchuyenbay_machuyenbay.Text
                                                   + "','" + xuly.taobang(sql1).Rows[0][0].ToString() + "','"
                                                  + dataGridView_sanbaytrunggian.Rows[i].Cells[1].Value.ToString() + "','"
                                                     + dataGridView_sanbaytrunggian.Rows[i].Cells[2].Value.ToString() + "')";
                                xuly.nonquery(sql2);
                            }
                            catch (System.Exception ex)
                            {
                                MessageBox.Show("Đã tồn tại chuyến bay này", "Lỗi");
                                return;
                            }

                        }
                    }
                   string sql3 = "select RTRIM(MaSanBay) as MaSanBay from SANBAY where TenSanBay = N'"
                                                + cb_nhanlichchuyenbay_sanbaydi.Text + "'";
                   string sql4 = "select RTRIM(MaSanBay) as MaSanBay from SANBAY where TenSanBay = N'"
                                               + cb_nhanlichchuyenbay_sanbayden.Text + "'";
                   //lưu tuyến bay nếu có sân mới ----------------------------------
                    try
                    {
                        string sql5="insert into TUYENBAY values('"+xuly.taobang(sql3).Rows[0][0].ToString()+"-"+xuly.taobang(sql4).Rows[0][0].ToString()
                            + "','" + xuly.taobang(sql3).Rows[0][0].ToString() + "','" + xuly.taobang(sql4).Rows[0][0].ToString()+"')";
                        xuly.nonquery(sql5);
                    }
                    catch (System.Exception ex)
                    {
                    	
                    }
                    //nhận báo cáo năm
                    
                    string sql7 = "insert into BAOCAODOANHTHUNAM values('"+ date.Year +"','0')";
                    try
                    {
                        xuly.nonquery(sql7);
                    }
                    catch (System.Exception ex)
                    {
                    	
                    }
                    //chi tiết năm
                    DataTable kq = new DataTable();
                    string kt1 = "SELECT * From CT_BCDT_NAM where Thang = N'" + date.Month + "'and Nam = N'" + date.Year+"'";
                    kq = xuly.taobang(kt1);
                    string sql8 ;
                    if (kq.Rows.Count == 0)
                        sql8 = "insert into CT_BCDT_NAM values('" + date.Month + "','" + date.Year + "','1','0','0')";
                    else
                        sql8 = "update CT_BCDT_NAM set SoChuyenBay = SoChuyenBay + 1 where Thang = N'" 
                            + date.Month + "' and Nam = N'" + date.Year + "'";
                    try
                    {
                        xuly.nonquery(sql8);
                    }
                    catch (System.Exception ex)
                    {
                    	
                    }
                    //bao cao doanh thu thang
                    string sql9 = "insert into BAOCAODOANHTHUTHANG values('" + date.Month + "-" + date.Year + "','" + date.Month + "','" + date.Year + "','0')";
                    try
                    {
                        xuly.nonquery(sql9);
                    }
                    catch (System.Exception ex)
                    {
                    	
                    }
                    //chi tiet bao cao thang
                    int tongghe = int.Parse(tb_nhanlichchuyenbay_soghe1.Text) + int.Parse(tb_nhanlichchuyenbay_soghe2.Text);
                    string sql10 = "insert into CT_BCDT_THANG values('" + date.Month + "-" + date.Year + "','" + tb_nhanlichchuyenbay_machuyenbay.Text + "','"+
                        tongghe +"','0','0')";
                    try
                    {
                        xuly.nonquery(sql10);
                    }
                    catch (System.Exception ex)
                    {
                    	
                    }
                    // lưu chuyến bay --------------------------------------
                    string sql6 = "insert into CHUYENBAY values('" + tb_nhanlichchuyenbay_machuyenbay.Text + "','"
                        + xuly.taobang(sql3).Rows[0][0].ToString() + "-" + xuly.taobang(sql4).Rows[0][0].ToString() + "','"
                        + nhanlichchuyenbay_ngay.Text+" "+tb_nhanlichchuyenbay_gio.Text + "','"
                        + tb_nhanlichchuyenbay_thoigianbay.Text + "','"
                        + tb_nhanlichchuyenbay_giasan.Text + "')";

                    try
                    {
                        xuly.nonquery(sql6);
                        MessageBox.Show("Đã nhận lịch chuyến bay", "Báo cáo");
                    }
                    catch (System.Exception ex)
                    {
                        MessageBox.Show("Đã tồn tại chuyến bay này\n"+ex.Message, "Lỗi !");
                    }
                    //chi tiet ghe
                    string tilegia1 = "select TiLeTinhDonGia from HANGVE where HangVe = '1'";
                    string tilegia2 = "select TiLeTinhDonGia from HANGVE where HangVe = '2'";
                    string sql11 = "insert into CT_GHE values('" 
                                   + tb_nhanlichchuyenbay_machuyenbay.Text + "','1','"
                                    + float.Parse(tb_nhanlichchuyenbay_giasan.Text) * float.Parse(xuly.taobang(tilegia1).Rows[0][0].ToString()) + "','"
                                    + tb_nhanlichchuyenbay_soghe1.Text + "','0','"
                                    + tb_nhanlichchuyenbay_soghe1.Text + "')";
                    string sql12 = "insert into CT_GHE values('" + tb_nhanlichchuyenbay_machuyenbay.Text + "','2','"
                                    + float.Parse(tb_nhanlichchuyenbay_giasan.Text) * float.Parse(xuly.taobang(tilegia2).Rows[0][0].ToString()) + "','"
                                    + tb_nhanlichchuyenbay_soghe2.Text + "','0','"
                                    + tb_nhanlichchuyenbay_soghe2.Text + "')";
                    try
                    {

                        xuly.nonquery(sql11);
                        xuly.nonquery(sql12);
                    }
                    catch (System.Exception ex)
                    {
                        MessageBox.Show("Thông tin lỗi \n" + ex.Message, "Lỗi");
                    }
                    
                }
                
            }
           
        }

        //load nhập liệu tra cứu chuyến bay ------------------------------------------------------------------
        private void tab_tra_cuu_chuyen_bay_Click(object sender, EventArgs e)
        {
            xulydulieu xuly = new xulydulieu();
            string laysanbay = "select TenSanBay from SANBAY";
            cb_tracuu_sanbaydi.DataSource = xuly.taobang(laysanbay);
            cb_tracuu_sanbaydi.DisplayMember = "TenSanBay";
            cb_tracuu_sanbayden.DataSource = xuly.taobang(laysanbay);
            cb_tracuu_sanbayden.DisplayMember = "TenSanBay";
        }
        //đảo chuỗi---------------------------------------------------
        string daochuoi(string a)
        {
            char[] b = a.ToCharArray();
            string nam = "";
            string thang = "";
            string ngay = "";
            int t =0;
            for (int i = 0; i < a.Length; i++)
            {
                
                if (b[i].ToString() == "/")
                {
                    t = i + 1; break;
                }
                thang += b[i].ToString();
            }
            for (int j = t; j < a.Length; j++)
            {
                
                if (b[j].ToString() == "/")
                {
                    t = j + 1; break;
                }
                ngay += b[j].ToString();
            }
            for (int x = t; x < a.Length; x++)
                nam += b[x].ToString();
            return nam + thang + ngay;
        }
        //tra cứu chuyến bay ------------------------------------------------------------
        private void bt_tracuuchuyenbay_Click(object sender, EventArgs e)
        {
            if (cb_tracuu_sanbaydi.Text == cb_tracuu_sanbayden.Text)
            {
                MessageBox.Show("Sân bay đến phải khác sân bay đi", "Chú ý");
            }
            else
            {
                if (cb_tracuu_ngaydi.Text == "" || cb_tracuu_ngayden.Text == "")
                {
                    MessageBox.Show("chưa chọn ngày đi hoăc ngày đến", "Chú ý !");
                }
                else
                {
                    if (cb_tracuu_ngaydi.Value == cb_tracuu_ngayden.Value)
                    {
                        MessageBox.Show("Ngày đi phải khác ngày đến", "Chú ý !");
                    }
                    else
                    {
                        try
                        {
                            xulydulieu xuly = new xulydulieu();
                            string madi = "select MaSanBay from SANBAY where TenSanBay= N'" + cb_tracuu_sanbaydi.Text + "'";
                            string maden = "select MaSanBay from SANBAY where TenSanBay= N'" + cb_tracuu_sanbayden.Text + "'";
                            string sql = "select cb.MaChuyenBay, Convert(nchar(10),NgayGioKhoiHanh, 103) , ThoiLuong,HangVe,SoGheDat,SoGheTrong from " +
                                "CHUYENBAY cb inner join TUYENBAY tb on cb.MaTuyenBay = tb.MaTuyenBay " +
                                "inner join CT_GHE ctg on cb.MaChuyenBay = ctg.MaCHuyenBay " +
                                "where " +
                                "tb.MaSanBayDi =N'" + xuly.taobang(madi).Rows[0][0].ToString() + "' and " +
                                "tb.MaSanBayDen =N'" + xuly.taobang(maden).Rows[0][0].ToString() + "' and " +
                                "convert(nchar(10),cb.NgayGioKhoiHanh,112) >= '" + daochuoi(cb_tracuu_ngaydi.Text) +
                                "' and convert(nchar(10),cb.NgayGioKhoiHanh,112) <= '" + daochuoi(cb_tracuu_ngayden.Text) +"'";
                            dataGridView_tracuuchuyenbay.DataSource = xuly.AutoNumberedTable(xuly.taobang(sql));
                            dataGridView_tracuuchuyenbay.Columns[1].HeaderText = "Mã Chuyến Bay";
                            dataGridView_tracuuchuyenbay.Columns[2].HeaderText = "Ngày Khởi Hành";
                            dataGridView_tracuuchuyenbay.Columns[3].HeaderText = "Thời Lượng";
                            dataGridView_tracuuchuyenbay.Columns[4].HeaderText = "Hạng Vé";
                            dataGridView_tracuuchuyenbay.Columns[5].HeaderText = "Số Ghế Đặt";
                            dataGridView_tracuuchuyenbay.Columns[6].HeaderText = "Số Ghế Trống";
                        }
                        catch (System.Exception ex)
                        {
                            MessageBox.Show(ex.Message);
                        }

                    }
                }

            }
        }
        //trả về giá trị sân bay trung gian--------------------------------------------------
        int dongchon;
        int a = 0;
        public void tensantrunggian( string value)
        {
            dataGridView_sanbaytrunggian.Rows[dongchon].Cells[0].Value = value;
        }
        public void tbthoigiandung( string value)
        {
            dataGridView_sanbaytrunggian.Rows[dongchon].Cells[1].Value = value;
        }
        public void tbghichu(string value)
        {
            dataGridView_sanbaytrunggian.Rows[dongchon].Cells[2].Value = value;
        }
        private void dataGridView_sanbaytrunggian_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            if (a == 0)
            {
                a = 1;
            }
            else
            {
                dongchon = e.RowIndex;
                chonsan themsan = new chonsan();
                themsan.tensan = new chonsan.giatritrave(tensantrunggian);
                themsan.thoigiandung = new chonsan.giatritrave(tbthoigiandung);
                themsan.ghichu = new chonsan.giatritrave(tbghichu);
                themsan.ShowDialog();
            }


        }

        private void bt_nhap_lich_chuyen_bay_Click(object sender, EventArgs e)
        {
            tb_nhanlichchuyenbay_machuyenbay.Clear();
            tb_nhanlichchuyenbay_giasan.Clear();
            tb_nhanlichchuyenbay_gio.Clear();
            tb_nhanlichchuyenbay_soghe1.Clear();
            tb_nhanlichchuyenbay_soghe2.Clear();
            tb_nhanlichchuyenbay_thoigianbay.Clear();

            xulydulieu xuly = new xulydulieu();
            dataGridView_sanbaytrunggian.Rows.Clear();
            a = 0;
            string sql = "select SoSanBayTrungGianToiDa from THAMSO";
            dataGridView_sanbaytrunggian.Rows.Add((int)xuly.taobang(sql).Rows[0][0] - 1);
        }

        private void tab_phong_ve_Click(object sender, EventArgs e)
        {
            xulydulieu xuly = new xulydulieu();
            string sql = "select MaChuyenBay from CHUYENBAY where NgayGioKhoiHanh > '"+DateTime.Now.ToString()+"'";
            cb_phieudatcho_machuyenaby.DataSource = xuly.taobang(sql);
            cb_phieudatcho_machuyenaby.DisplayMember = "MaChuyenBay";
        }

        private void tab_ve_chuyen_bay_Click(object sender, EventArgs e)
        {
            xulydulieu xuly = new xulydulieu();
            string sql = "select MaChuyenBay from CHUYENBAY where NgayGioKhoiHanh > '" + DateTime.Now.ToString() + "'";
            cb_vechuyenbay_machuyenbay.DataSource = xuly.taobang(sql);
            cb_vechuyenbay_machuyenbay.DisplayMember = "MaChuyenBay";

        }

        private void bt_vechuyenbay_nhapmoi_Click(object sender, EventArgs e)
        {
            tb_vechuyenbay_cmnd.Clear();
            tb_vechuyenbay_dienthoai.Clear();
            tb_vechuyenbay_hanhkhach.Clear();
            tb_vechuyenbay_mahanhkhach.Clear();
        }

        private void tb_phieudatcho_nhaplai_Click(object sender, EventArgs e)
        {
            tb_phieudatcho_dienthoai.Clear();
            tb_phieudatcho_hanhkhach.Clear();
            tb_phieudatcho_mahanhkhach.Clear();
            tb_phieudatcho_cmnd.Clear();
        }
        // đặt chỗ ---------------------------------------------------------
        private void tb_phieudatcho_luu_Click(object sender, EventArgs e)
        {
            if (cb_phieudatcho_machuyenaby.Text == null || cb_phieudatcho_ngaydat.Text==""||
                tb_phieudatcho_cmnd.Text==""||tb_phieudatcho_dienthoai.Text==""||
                tb_phieudatcho_hanhkhach.Text==""||hangve_phieudatcho_hangve.Text=="")
                MessageBox.Show("chưa điền đầy đủ thông tin", "Lỗi");
            else
            {
                   xulydulieu xuly = new xulydulieu();
                   string sql3 = "select SoGheTrong from CT_GHE where MaChuyenBay =N'" + cb_phieudatcho_machuyenaby.Text
                       + "' and HangVe =N'" + hangve_phieudatcho_hangve.Text + "'";
                   if(int.Parse(xuly.taobang(sql3).Rows[0][0].ToString())==0)
                   {
                       MessageBox.Show("Đã hết ghế trống", "Thông báo");
                       return;
                   }

                try
                {                 
                    string sql = "insert into PHIEUDATCHO values('" + tb_phieudatcho_cmnd.Text+"-"+cb_phieudatcho_machuyenaby.Text + "','" +
                        cb_phieudatcho_ngaydat.Text + "','" + tb_phieudatcho_hanhkhach.Text + "','" +
                        tb_phieudatcho_cmnd.Text + "','" + tb_phieudatcho_dienthoai.Text + "')";
                    xuly.nonquery(sql);

                    string sql1 = "insert into CT_PHIEUDATCHO values('"+tb_phieudatcho_cmnd.Text+"-"+cb_phieudatcho_machuyenaby.Text+"','"+
                        cb_phieudatcho_machuyenaby.Text+"','"+tb_phieudatcho_hanhkhach.Text+"','"+
                        tb_phieudatcho_cmnd.Text+"','"+tb_phieudatcho_dienthoai.Text+"','"+
                        hangve_phieudatcho_hangve.Text+"','"+tb_phieudatcho_giatien.Text+"','false')";
                    xuly.nonquery(sql1);

                    MessageBox.Show("Đã đặt vé thành công", "báo cáo !");
                    string sql2 = "update CT_GHE set SoGheDat = SoGheDat + 1, SoGheTrong = SoGheTrong - 1 where "+
                        "MaChuyenBay = N'"+ cb_phieudatcho_machuyenaby.Text+"' and HangVe = N'" +hangve_phieudatcho_hangve.Text+"'";
                    xuly.nonquery(sql2);

                }
                catch (System.Exception ex)
                {
                    MessageBox.Show("có thể bạn đã đặt chỗ rồi", "Lỗi");
                }
            }
        }
        int c = 0;
        private void hangve_phieudatcho_hangve_SelectedIndexChanged(object sender, EventArgs e)
        {
                c = 1;
                try
                {
                    xulydulieu xuly = new xulydulieu();
                    string sql = "select GiaTien from CT_GHE " +
                        "where MaChuyenBay = N'" + cb_phieudatcho_machuyenaby.Text + "' and " +
                        "HangVe = '" + hangve_phieudatcho_hangve.Text + "'";
                    tb_phieudatcho_giatien.Text = xuly.taobang(sql).Rows[0][0].ToString().Substring(0, xuly.taobang(sql).Rows[0][0].ToString().Length-5);
                }
                catch (System.Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

        }
        // mua vé ------------------------------------------------------------------
        private void bt_vechuyenbay_luu_Click(object sender, EventArgs e)
        {
            if(cb_vechuyenbay_hangve.Text=="" ||cb_vechuyenbay_machuyenbay.Text==null||
                tb_vechuyenbay_cmnd.Text==""||tb_vechuyenbay_dienthoai.Text==""||
                tb_vechuyenbay_hanhkhach.Text==""||tb_vechuyenbay_mahanhkhach.Text=="")
                MessageBox.Show("Chua dien day du thong tin","Loi");
            else
            {
                xulydulieu xuly = new xulydulieu();
                string kt = "select SoGheTrong from CT_GHE where MaChuyenBay =N'" + cb_vechuyenbay_machuyenbay.Text
                              + "' and HangVe =N'" + cb_vechuyenbay_hangve.Text + "'";
                if (int.Parse(xuly.taobang(kt).Rows[0][0].ToString()) == 0)
                {
                    MessageBox.Show("Đã hết ghế trống", "Thông báo");
                    return;
                }
                try
                {
                    string kt2 = "select LayVe from CT_PHIEUDATCHO where MaPhieuDatCho=N'" + tb_vechuyenbay_cmnd.Text +
                        cb_vechuyenbay_machuyenbay.Text + "'";
                    if (xuly.taobang(kt2).Rows[0][0].ToString() == "True")
                    {
                        MessageBox.Show("Ban da mua ve truoc do roi"); return;
                    }
                }
                catch (System.Exception ex)
                {
                	
                }
       
                try
                {
 
                    string sql = "select * from CT_PHIEUDATCHO where MaPhieuDatCho=N'" + tb_vechuyenbay_cmnd.Text + cb_vechuyenbay_machuyenbay.Text + "'";
                    DataTable xet = new DataTable();
                    xet = xuly.taobang(sql);

                    string sql_thang = "select MONTH(NgayGioKhoiHanh) from CHUYENBAY where MaChuyenBay =N'" + cb_vechuyenbay_machuyenbay.Text + "'";
                    string thang = xuly.taobang(sql_thang).Rows[0][0].ToString();
                    
                    string sql_nam = "select YEAR(NgayGioKhoiHanh) from CHUYENBAY where MaChuyenBay =N'" + cb_vechuyenbay_machuyenbay.Text + "'";
                    string nam = xuly.taobang(sql_nam).Rows[0][0].ToString();

                    if (xet.Rows.Count == 1)
                    {
                        if (xet.Rows[0][5].ToString() != cb_vechuyenbay_hangve.Text)
                        {
                            MessageBox.Show("Bạn đã đặt một ghế hạng khác", "Lưu ý"); return;
                        }
                        string sql1 = "update CT_PHIEUDATCHO set LayVe = 'True' where MaPhieuDatCho = N'" + tb_vechuyenbay_cmnd.Text + cb_vechuyenbay_machuyenbay.Text + "'";
                        xuly.nonquery(sql1);
                        string sql11 = "update CT_GHE set SoGheDat = SoGheDat - 1 where MaChuyenBay = '"+cb_vechuyenbay_machuyenbay.Text+
                            "' and HangVe = '"+cb_vechuyenbay_hangve.Text+"'";
                        xuly.nonquery(sql11);
                    }
                    else
                    {
                        string sql2 = "insert into PHIEUDATCHO values('" + tb_vechuyenbay_cmnd.Text + "-"+ cb_vechuyenbay_machuyenbay.Text + "','" +
                                DateTime.Now.Month + "/" + DateTime.Now.Day + "/" + DateTime.Now.Year + "','" + tb_vechuyenbay_hanhkhach.Text + "','" +
                                tb_vechuyenbay_cmnd.Text + "','" + tb_vechuyenbay_dienthoai.Text + "')";
                        xuly.nonquery(sql2);
                        string sql3 = "insert into CT_PHIEUDATCHO values('" + tb_vechuyenbay_cmnd.Text +"-"+ cb_vechuyenbay_machuyenbay.Text + "','" +
                            cb_vechuyenbay_machuyenbay.Text + "','" + tb_vechuyenbay_hanhkhach.Text + "','" +
                            tb_phieudatcho_cmnd.Text + "','" + tb_phieudatcho_dienthoai.Text + "','" +
                            cb_vechuyenbay_hangve.Text + "','" + tb_vechuyenbay_giatien.Text + "','True')";
                        xuly.nonquery(sql3);
                        string sql4 = "update CT_GHE set SoGheTrong = SoGheTrong - 1 where " +
                            "MaChuyenBay = N'" + cb_phieudatcho_machuyenaby.Text + "' and HangVe = N'" + hangve_phieudatcho_hangve.Text + "'";
                        xuly.nonquery(sql4);

                    }
                    string sql5 = "update BAOCAODOANHTHUNAM set TongDoanhThu = TongDoanhThu +" +
                            int.Parse(tb_vechuyenbay_giatien.Text) + " where Nam =N'" + nam + "'";
                    xuly.nonquery(sql5);

                    string layge1 = "select ct.SoGhe from CT_GHE ct inner join CHUYENBAY cb on ct.MaChuyenBay = cb.MaChuyenBay where cb.MaChuyenBay =N'"+
                        cb_vechuyenbay_machuyenbay.Text+"' and ct.HangVe ='1'";

                    string layge2 = "select ct.SoGhe from CT_GHE ct inner join CHUYENBAY cb on ct.MaChuyenBay = cb.MaChuyenBay where cb.MaChuyenBay =N'" +
                        cb_vechuyenbay_machuyenbay.Text + "' and ct.HangVe ='2'";
                    double tile = 1.0 /(float) (int.Parse(xuly.taobang(layge1).Rows[0][0].ToString()) + int.Parse(xuly.taobang(layge2).Rows[0][0].ToString()));

                    string sql6 = "update CT_BCDT_NAM set DoanhThu = DoanhThu + "+ int.Parse(tb_vechuyenbay_giatien.Text)+
                        " where Thang = '"+ thang +"' and Nam = '"+nam+"'";
                    xuly.nonquery(sql6);

                    string sql7 = "update BAOCAODOANHTHUTHANG set TongDoanhThu = TongDoanhThu + " + int.Parse(tb_vechuyenbay_giatien.Text)
                        + " where Thang = '" +thang +"' and Nam = '"+nam+"'";
                    xuly.nonquery(sql7);

                    string sql8 = "update CT_BCDT_THANG set DoanhThu = DoanhThu + "+ int.Parse(tb_vechuyenbay_giatien.Text)+
                         " , TiLe = Tile +"+ tile +" where MaChuyenBay = N'"+ cb_vechuyenbay_machuyenbay.Text+"'";
                    xuly.nonquery(sql8);
                    MessageBox.Show("Đã mua vé thành công");
                }
                catch (System.Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
               
            }
        }

        int b = 0;
        private void cb_phieudatcho_machuyenaby_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (b == 0)
                b = 1;
            else
            {
                try
                {
                    xulydulieu xuly = new xulydulieu();
                    string sql = "select sb.TenSanBay from SANBAY sb " +
                        "inner join TUYENBAY tb on sb.MaSanBay = tb.MaSanBayDi " +
                        "inner join CHUYENBAY cb on cb.MaTuyenBay = tb.MaTuyenBay " +
                        "where cb.MaChuyenBay = N'" + cb_phieudatcho_machuyenaby.Text + "'";
                    tb_phieudatcho_sanbaydi.Text = xuly.taobang(sql).Rows[0][0].ToString();

                    string sql1 = "select sb.TenSanBay from SANBAY sb " +
                      "inner join TUYENBAY tb on sb.MaSanBay = tb.MaSanBayDen " +
                      "inner join CHUYENBAY cb on cb.MaTuyenBay = tb.MaTuyenBay " +
                      "where cb.MaChuyenBay = N'" + cb_phieudatcho_machuyenaby.Text + "'";
                    tb_phieudatcho_sanbayden.Text = xuly.taobang(sql1).Rows[0][0].ToString();

                    string sql2 = "select NgayGioKhoiHanh from CHUYENBAY where MaChuyenBay = N'" + cb_phieudatcho_machuyenaby.Text + "'";
                    tb_phieudatcho_ngaygio.Text = xuly.taobang(sql2).Rows[0][0].ToString();
                    if (c == 1)
                    {
                        string sql3 = "select GiaTien from CT_GHE " +
                            "where MaChuyenBay = N'" + cb_phieudatcho_machuyenaby.Text + "' and " +
                            "HangVe = '" + hangve_phieudatcho_hangve.Text + "'";
                        tb_phieudatcho_giatien.Text = xuly.taobang(sql3).Rows[0][0].ToString().Substring(0, xuly.taobang(sql3).Rows[0][0].ToString().Length-5);
                    }
                }
                catch (System.Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }

        }
        int d = 0;
        private void cb_vechuyenbay_machuyenbay_SelectedIndexChanged(object sender, EventArgs e)
        {
            xulydulieu xuly = new xulydulieu();
            string sql1 = "select sb.TenSanBay from SANBAY sb " +
                    "inner join TUYENBAY tb on sb.MaSanBay = tb.MaSanBayDi " +
                    "inner join CHUYENBAY cb on cb.MaTuyenBay = tb.MaTuyenBay " +
                    "where cb.MaChuyenBay = N'" + cb_vechuyenbay_machuyenbay.Text + "'";
            tb_vechuyenbay_sanbaydi.Text = xuly.taobang(sql1).Rows[0][0].ToString();

            string sql2 = "select sb.TenSanBay from SANBAY sb " +
              "inner join TUYENBAY tb on sb.MaSanBay = tb.MaSanBayDen " +
              "inner join CHUYENBAY cb on cb.MaTuyenBay = tb.MaTuyenBay " +
              "where cb.MaChuyenBay = N'" + cb_vechuyenbay_machuyenbay.Text + "'";
            tb_vechuyenbay_sanbayden.Text = xuly.taobang(sql2).Rows[0][0].ToString();

            string sql3 = "select NgayGioKhoiHanh from CHUYENBAY where MaChuyenBay = N'" + cb_vechuyenbay_machuyenbay.Text + "'";
            tb_vechuyenbay_ngaygio.Text = xuly.taobang(sql3).Rows[0][0].ToString();
            if (d == 1)
            {
                string sql4 = "select GiaTien from CT_GHE " +
                     "where MaChuyenBay = N'" + cb_vechuyenbay_machuyenbay.Text + "' and " +
                     "HangVe = '" + cb_vechuyenbay_hangve.Text + "'";
                tb_vechuyenbay_giatien.Text = xuly.taobang(sql4).Rows[0][0].ToString().Substring(0, xuly.taobang(sql4).Rows[0][0].ToString().Length-5);
            }
        }

        private void cb_vechuyenbay_hangve_SelectedIndexChanged(object sender, EventArgs e)
        {
            d = 1;
            try
            {
                xulydulieu xuly = new xulydulieu();
                string sql = "select GiaTien from CT_GHE " +
                    "where MaChuyenBay = N'" + cb_vechuyenbay_machuyenbay.Text + "' and " +
                    "HangVe = '" + cb_vechuyenbay_hangve.Text + "'";
                tb_vechuyenbay_giatien.Text = xuly.taobang(sql).Rows[0][0].ToString().Substring(0, xuly.taobang(sql).Rows[0][0].ToString().Length-5);
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void tab_quan_ly_chuyen_bay_Click(object sender, EventArgs e)
        {
            xulydulieu xuly = new xulydulieu();
            string sql = "select RTRIM(MaChuyenBay) as MaChuyenBay, MaTuyenBay,NgayGioKhoiHanh,ThoiLuong,GiaSan from CHUYENBAY";
            dataGridView_quanlychuyenbay.DataSource = xuly.taobang(sql);
            dataGridView_quanlychuyenbay.Columns[0].HeaderText = "Mã chuyến bay";
            dataGridView_quanlychuyenbay.Columns[1].HeaderText = "Mã tuyến bay";
            dataGridView_quanlychuyenbay.Columns[2].HeaderText = "Ngày giờ khởi hành";
            dataGridView_quanlychuyenbay.Columns[3].HeaderText = "Thời lượng";
            dataGridView_quanlychuyenbay.Columns[4].HeaderText = "Giá sàn";
        }

        private void tb_phieudatcho_cmnd_TextChanged(object sender, EventArgs e)
        {

        }

        private void tb_phieudatcho_cmnd_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (!Char.IsDigit(e.KeyChar) && !Char.IsControl(e.KeyChar))
                e.Handled = true;
        }

        private void tb_phieudatcho_cmnd_KeyUp(object sender, KeyEventArgs e)
        {
            tb_phieudatcho_mahanhkhach.Text = tb_phieudatcho_cmnd.Text;
        }

        private void tb_vechuyenbay_cmnd_KeyUp(object sender, KeyEventArgs e)
        {
            tb_vechuyenbay_mahanhkhach.Text = tb_vechuyenbay_cmnd.Text;
        }

        private void tab_bao_cao_Click(object sender, EventArgs e)
        {
            xulydulieu xuly = new xulydulieu();
            string sql = "select Nam from BAOCAODOANHTHUNAM";
            baocao_nam.DataSource = xuly.taobang(sql);
            baocao_nam.DisplayMember = "Nam";
            bc_nam.DataSource = xuly.taobang(sql);
            bc_nam.DisplayMember = "Nam";
        }

        private void xuat_baocaothang_Click(object sender, EventArgs e)
        {
            if (baocao_thang.Text == "")
                MessageBox.Show("chưa chọn tháng");
            else
            {
                try
                {
                    xulydulieu xuly = new xulydulieu();
                    string sql = "select ct.MaChuyenBay, ct.SoVe, ct.DoanhThu, ct.TiLe from "+
                        "CT_BCDT_THANG ct inner join BAOCAODOANHTHUTHANG bc on ct.MaBCDTT = bc.MaBCDTT "+
                        "where bc.Thang =N'"+baocao_thang.Text+"' and bc.Nam =N'"+baocao_nam.Text+"'";
                    dataGridView_dt_thang.DataSource = xuly.taobang(sql);
                    dataGridView_dt_thang.Columns[0].HeaderText = "Mã Chuyến Bay";
                    dataGridView_dt_thang.Columns[1].HeaderText = "Số Vé";
                    dataGridView_dt_thang.Columns[2].HeaderText = "Doanh Thu";
                    dataGridView_dt_thang.Columns[3].HeaderText = "Tỉ Lệ";
                    string sql1 = "select TongDoanhThu from BAOCAODOANHTHUTHANG where " +
                        "Thang =N'" + baocao_thang.Text + "' and Nam =N'" + baocao_nam.Text + "'";
                    string a = xuly.taobang(sql1).Rows[0][0].ToString();
                    tb_tong_dt_thang.Text = a.Substring(0,a.Length-5);
                }
                catch (System.Exception ex)
                {
                  //  MessageBox.Show(ex.Message);
                }

            }
        }

        private void xuatbaocao_nam_Click(object sender, EventArgs e)
        {
            try
            {
                xulydulieu xuly = new xulydulieu();
                string sql = "select Thang, Nam, SoChuyenBay, DoanhThu from CT_BCDT_NAM where Nam = N'" + bc_nam.Text + "'";
                dataGridView_dt_nam.DataSource = xuly.taobang(sql);
                string sql1 = "select TongDoanhThu from BAOCAODOANHTHUNAM where Nam ='" + bc_nam.Text+"'";
                tb_tong_dt_nam.Text = xuly.taobang(sql1).Rows[0][0].ToString().Substring(0, xuly.taobang(sql1).Rows[0][0].ToString().Length-5);
                dataGridView_dt_nam.Columns[0].HeaderText = "Tháng";
                dataGridView_dt_nam.Columns[1].HeaderText = "Năm";
                dataGridView_dt_nam.Columns[2].HeaderText = "Số Chuyến Bay";
                dataGridView_dt_nam.Columns[3].HeaderText = "Doanh Thu";
            }
            catch (System.Exception ex)
            {
            	
            }

        }

        private void tb_nhanlichchuyenbay_soghe1_KeyPress(object sender, KeyPressEventArgs e)
        {
              if (!Char.IsDigit(e.KeyChar) && !Char.IsControl(e.KeyChar))
                e.Handled = true;
        }

        private void tb_nhanlichchuyenbay_soghe2_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (!Char.IsDigit(e.KeyChar) && !Char.IsControl(e.KeyChar))
                e.Handled = true;
        }

        private void tb_nhanlichchuyenbay_giasan_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (!Char.IsDigit(e.KeyChar) && !Char.IsControl(e.KeyChar))
                e.Handled = true;
        }

        private void tb_vechuyenbay_cmnd_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (!Char.IsDigit(e.KeyChar) && !Char.IsControl(e.KeyChar))
                e.Handled = true;
        }

        private void tb_vechuyenbay_dienthoai_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (!Char.IsDigit(e.KeyChar) && !Char.IsControl(e.KeyChar))
                e.Handled = true;
        }

        private void tb_phieudatcho_dienthoai_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (!Char.IsDigit(e.KeyChar) && !Char.IsControl(e.KeyChar))
                e.Handled = true;
        }

        private void sosan_trunggiantoida_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (!Char.IsDigit(e.KeyChar) && !Char.IsControl(e.KeyChar))
                e.Handled = true;
        }

       
        private void dataGridView_quanlychuyenbay_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            curen_row = e.RowIndex;
        }
        private void bt_quanlychuyenbay_xoa_Click(object sender, EventArgs e)
        {

            xulydulieu xuly = new xulydulieu();
            string sql = "select Convert(nchar(10),NgayGioKhoiHanh, 112) from CHUYENBAY where MaChuyenBay ='" 
                + dataGridView_quanlychuyenbay.Rows[curen_row].Cells[0].Value.ToString() + "'";
            string ngay = xuly.taobang(sql).Rows[0][0].ToString();
            string now = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString();
            if (string.Compare(now, ngay) >= 0)
            {
                MessageBox.Show("chỉ xóa chuyến bay trước ngày khởi hành", "Lỗi !");
            }
            else
            {
                try
                {
                    string sql1 = "select TenHanhKhach, DienThoai from CT_PHIEUDATCHO where MaChuyenBay = '" + 
                        dataGridView_quanlychuyenbay.Rows[curen_row].Cells[0].Value.ToString()+"'";
                    ThongBao form3 = new ThongBao();
                    form3.dataGridView_ds_huy.DataSource = xuly.taobang(sql1);
                    form3.ShowDialog();
                    string mabay = dataGridView_quanlychuyenbay.Rows[curen_row].Cells[0].Value.ToString();
                    string sql2 = "delete from CT_CHUYENBAY where MaChuyenBay ='" + mabay + "'";
                    xuly.nonquery(sql2);

                    string sql33 = "delete from CT_PHIEUDATCHO where MaPhieuDatCho like '%-" +mabay+ "%'";
                    xuly.nonquery(sql33);

                    string sql3 = "delete from PHIEUDATCHO where SoPhieuDatCho like '%-" +mabay + "%'";
                    xuly.nonquery(sql3);
                    
                    string sql4 = "delete from CT_GHE where MaChuyenBay ='"+mabay + "'";
                    xuly.nonquery(sql4);

                    string sql5 = "delete from CHUYENBAY where MaChuyenBay = '"+mabay + "'";
                    xuly.nonquery(sql5);

                    string sql6 = "delete from CT_BCDT_THANG where MaChuyenBay ='"+mabay + "'";
                    xuly.nonquery(sql6);

                    MessageBox.Show("Đã xóa chuyến bay");

                    string sql7 = "select * from CHUYENBAY";
                    dataGridView_quanlychuyenbay.DataSource = xuly.taobang(sql7);
                }
                catch (System.Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

            }

        }

        private void Tab_nhan_lich_chuyen_bay_Click(object sender, EventArgs e)
        {
            xulydulieu xuly = new xulydulieu();
            // load tab nhan lich chuyen bay
            string laysanbay = "select TenSanBay from SANBAY";
            cb_nhanlichchuyenbay_sanbaydi.DataSource = xuly.taobang(laysanbay);
            cb_nhanlichchuyenbay_sanbaydi.DisplayMember = "TenSanBay";
            cb_nhanlichchuyenbay_sanbayden.DataSource = xuly.taobang(laysanbay);
            cb_nhanlichchuyenbay_sanbayden.DisplayMember = "TenSanBay";
        }

        private void dataGridView_taikhoan_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                int dong;
                dong = e.RowIndex;
                tb_taikhoan_phanquyen.Text = dataGridView_taikhoan.Rows[dong].Cells[0].Value.ToString();
            }
            catch (System.Exception ex)
            {
            	
            }
         
        }


 

    }
}
